
import pandas as pd

df = pd.read_excel("pr1.xlsx", sheet_name='Variant_Details')

# Check the total number of unique genes in the 'GENE' column
unique_genes = df['GENE'].nunique()
print(f'Total number of unique genes: {unique_genes}')

Total number of unique genes: 738


import pandas as pd
import plotly.express as px


# Get the top 10 genes with the highest occurrences
top_genes = df['GENE'].value_counts().head(10)
top_genes_df = df[df['GENE'].isin(top_genes.index)]

# Create a new DataFrame to store the patient information for each gene
hover_data = top_genes_df.groupby('GENE')['Contact_ID'].apply(list).reset_index()
hover_data['Contact_ID'] = hover_data['Contact_ID'].apply(lambda x: ', '.join(x))

# Merge the data for plotting
plot_data = top_genes.reset_index()
plot_data = plot_data.merge(hover_data, left_on='index', right_on='GENE')

# Create the interactive bar chart
fig = px.bar(plot_data, x='index', y='GENE_x', text='GENE_x', hover_name='Contact_ID', title='Top 10 Genes with Highest Occurrences')
fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(xaxis_title='Gene', yaxis_title='Frequency', showlegend=False)
fig.show()
fig.write_html("top 10 genes.html")


from dash import Dash, dcc, html
from dash.dependencies import Input, Output
import plotly.graph_objects as go
import pandas as pd

# Sample DataFrame (replace this with loading your actual data)


# Initialize the Dash app
app = Dash(__name__)

# Define the layout of the app
app.layout = html.Div([
    dcc.Input(id='gene-input', type='text', placeholder='Enter a gene'),
    html.Button('Submit', id='submit-button', n_clicks=0),
    dcc.Graph(id='gene-bar-plot'),
    html.Div(id='patient-list')
])

# Define the callback to update the graph and display patient list
@app.callback(
    [Output('gene-bar-plot', 'figure'),
     Output('patient-list', 'children')],
    [Input('submit-button', 'n_clicks')],
    [Input('gene-input', 'value')]
)
def update_graph(n_clicks, gene_input):
    if n_clicks > 0 and gene_input:
        filtered_df = df[df['GENE'] == gene_input]
        gene_count = len(filtered_df)
        patients = filtered_df['Contact_ID'].tolist()
        
        # Create bar plot
        fig = go.Figure(data=[go.Bar(x=[gene_input], y=[gene_count], text=[gene_count])])
        fig.update_traces(texttemplate='%{text}', textposition='outside')
        fig.update_layout(xaxis_title='Gene', yaxis_title='Count', showlegend=False)
        
        # Create patient list
        patient_list = html.Ul([html.Li(patient) for patient in patients])
        
        return fig, patient_list
    return go.Figure(), None

# Run the app
if __name__ == '__main__':
    app.run_server(debug=True)


# Function to update the plot based on the selected gene
import pandas as pd
import plotly.express as px
from ipywidgets import widgets
from IPython.display import display

df_variant_details = pd.read_excel("pr1.xlsx", sheet_name='Variant_Details')
df_patient_results = pd.read_excel("pr1.xlsx", sheet_name='Patient_Results')

def plot_gene_distribution(gene):
    # Merge dataframes
    df_merged = pd.merge(df_patient_results, df_variant_details, on='Contact_ID')
    
    # Filter based on selected gene
    df_filtered = df_merged[df_merged['GENE'] == gene]
    
    # Group by state and count occurrences
    state_counts = df_filtered['Institution_ST'].value_counts().reset_index()
    state_counts.columns = ['State', 'Count']
    
    # Plot
    fig = px.choropleth(state_counts, 
                        locations='State', 
                        color='Count',
                        locationmode="USA-states", 
                        scope="usa",
                        title=f'Distribution of {gene} Across States')
    fig.show()

# Create widget
gene_widget = widgets.Combobox(
    options=df_variant_details['GENE'].unique().tolist(),
    value='SAMHD1',
    description='Select Gene:',
)

# Display widget
widgets.interactive(plot_gene_distribution, gene=gene_widget)

interactive(children=(Combobox(value='SAMHD1', description='Select Gene:', options=('SAMHD1', 'KCNQ2', 'ADAR',…


#Zygosity

import ipywidgets as widgets
import plotly.express as px
import pandas as pd

# Calculate the frequency of each category in 'ZYGOSITY'
zygosity_counts = df['ZYGOSITY'].value_counts()
unique_zygosities = df['ZYGOSITY'].nunique()

# Display the number of unique values
zygosity_text = widgets.HTML(value=f"<b>Number of Unique Zygosity Values:</b> {unique_zygosities}")
display(zygosity_text)

# Create the interactive bar chart
fig = px.bar(zygosity_counts, title='Frequency of Each Zygosity Category')
fig.update_layout(xaxis_title='Zygosity', yaxis_title='Frequency', showlegend=False)
fig.show()

HTML(value='<b>Number of Unique Zygosity Values:</b> 10')


import plotly.graph_objects as go
from dash import Dash, dcc, html
from dash.dependencies import Input, Output
import pandas as pd

# Read the Excel file
df = pd.read_excel('pr1.xlsx', sheet_name = 'Variant_Details')

# Initialize the Dash app
app = Dash(__name__)

# Define the layout of the app
app.layout = html.Div([
    dcc.Dropdown(
        id='interpretation-type',
        options=[
            {'label': 'Original Interpretation', 'value': 'ORIGINAL_INTERPRETATION'},
            {'label': 'Revised Interpretation', 'value': 'REVISED_INTERPRETATION'}
        ],
        value='ORIGINAL_INTERPRETATION',
        multi=False
    ),
    dcc.Graph(id='bar-plot')
])

# Define the callback to update the bar plot
@app.callback(
    Output('bar-plot', 'figure'),
    [Input('interpretation-type', 'value')]
)
def update_bar_plot(selected_interpretation):
    # Filter the data based on the selected interpretation type
    interpretation_data = df[selected_interpretation].value_counts().reset_index()
    interpretation_data.columns = ['Category', 'Count']
    
    # Create the bar plot
    fig = go.Figure(go.Bar(
        x=interpretation_data['Count'],
        y=interpretation_data['Category'],
        orientation='h'
    ))
    fig.update_layout(
        title=f'Distribution of {selected_interpretation.replace("_", " ")}',
        xaxis_title='Count',
        yaxis_title='Category'
    )
    return fig

# Run the app
if __name__ == '__main__':
    app.run_server(debug=True)


#Heatmap showing how the revised interpretation was different from the original interpretation


import plotly.express as px
import pandas as pd

# Creating a DataFrame from the given data
data = {
    'Clinician': ['Atiya Khan', 'Shalaka Indulkar', 'Rebecca Luke', 'Mark Fitzgerald', 
                  'Jesus Pina-Garza', 'Bernardo Flasterstein', 'Jasna Kojic', 'Naeem Mahfooz', 
                  'Melissa Assaf', 'Peter Castellanos'],
    'Patients': [425, 260, 216, 215, 195, 191, 176, 175, 157, 156]
}

df = pd.DataFrame(data)

# Sorting the DataFrame based on the number of patients
df = df.sort_values(by='Patients', ascending=False)

# Creating the bar chart
fig = px.bar(df, x='Clinician', y='Patients', title='Top 10 Clinicians Based on Number of Patients Seen',
             labels={'Patients': 'Number of Patients'}, text='Patients')

# Showing the plot
fig.show()


import pandas as pd
import plotly.express as px

clinician_data = pd.read_excel("pr1.xlsx", sheet_name='Accessioned_By_Clinician')

import pandas as pd
import plotly.express as px

# Data Preparation
date_columns = clinician_data.columns[6:-2]
data_transposed = clinician_data.melt(id_vars='Clinician', value_vars=date_columns, var_name='Date', value_name='Patients')
data_transposed['Date'] = pd.to_datetime(data_transposed['Date'])
data_grouped = data_transposed.groupby(['Clinician', 'Date'])['Patients'].sum().reset_index()

# Identify Top Clinicians
top_clinicians = data_grouped.groupby('Clinician')['Patients'].sum().sort_values(ascending=False).head(10)

# Time Series Analysis
data_top_clinicians = data_grouped[data_grouped['Clinician'].isin(top_clinicians.index)]
data_top_clinicians['Year'] = data_top_clinicians['Date'].dt.year
data_top_clinicians['Quarter'] = data_top_clinicians['Date'].dt.to_period("Q").astype(str)
data_grouped_top_clinicians = data_top_clinicians.groupby(['Clinician', 'Year', 'Quarter'])['Patients'].sum().reset_index()

# Create an interactive line plot
fig = px.line(data_grouped_top_clinicians, x='Quarter', y='Patients', color='Clinician', 
              title='Trend of Number of Patients Seen by Top 10 Clinicians Over Each Quarter',
              labels={'Patients': 'Number of Patients', 'Quarter': 'Quarter'},
              template='plotly_white')

# Show the plot
fig.show()

/var/folders/l4/3ctxtv0s4f10lcsxz1mr2_080000gn/T/ipykernel_42380/3598726933.py:20: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/var/folders/l4/3ctxtv0s4f10lcsxz1mr2_080000gn/T/ipykernel_42380/3598726933.py:21: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


import pandas as pd
import plotly.express as px

# Read data
df_institute = pd.read_excel('pr1.xlsx', sheet_name='Accessioned_By_Institute')
df_clinician = pd.read_excel('pr1.xlsx', sheet_name='Accessioned_By_Clinician')

# Calculate total patients for each institute and clinician
df_institute['Total_Patients'] = df_institute.iloc[:, 5:].sum(axis=1)
df_clinician['Total_Patients'] = df_clinician.iloc[:, 6:].sum(axis=1)

# Get top 20 institutes
top_institutes = df_institute.nlargest(20, 'Total_Patients')

# Initialize an empty DataFrame to store top 5 clinicians for each top institute
top_clinicians = pd.DataFrame()

# For each top institute, find top 5 clinicians
for index, row in top_institutes.iterrows():
    institute_id = row['Institution ID']
    clinicians = df_clinician[df_clinician['Institution ID'] == institute_id]
    top_clinicians_in_institute = clinicians.nlargest(5, 'Total_Patients')
    top_clinicians = pd.concat([top_clinicians, top_clinicians_in_institute])

# Create an interactive bar chart
fig = px.bar(top_clinicians, x='Institution', y='Total_Patients', color='Clinician', title='Top 5 Clinicians in Top 20 Institutes')

# Show the plot
fig.show()


df = pd.read_excel("pr1.xlsx", sheet_name='Aggregate_Volumes_by_Age')

# Set the 'Age_Group' column as the index
df.set_index('Age_Group', inplace=True)

# Transpose the DataFrame to have dates as columns and age groups as rows
df = df.transpose()

# Convert the index to datetime objects
df.index = pd.to_datetime(df.index)

# Create an interactive time series plot using Plotly
fig = px.line(df, x=df.index, y=df.columns, title='Time Series Analysis: Trend Over Time for Each Age Group')
fig.update_xaxes(title_text='Date')
fig.update_yaxes(title_text='Count')

# Show the interactive plot
fig.show()


import pandas as pd
import plotly.express as px


df = pd.read_excel("pr1.xlsx", sheet_name="Accessioned_By_Institute")

# Calculate total number of patients for each institute
df['Total Patients'] = df.iloc[:, 5:].sum(axis=1)

# Sort institutes by total patients in descending order and select top 20
top_institutes = df.sort_values(by='Total Patients', ascending=False).head(20)

# Create interactive bar chart
fig = px.bar(top_institutes, x='Institution', y='Total Patients', title='Top 20 Institutes by Number of Patients')
fig.show()


# Read the data from Sheet2
# Load the Excel file
file_path = 'pr1.xlsx'
excel_data = pd.ExcelFile(file_path)
# Read the data from Sheet1
sheet1_data = excel_data.parse('Accessioned_By_Institute')

sheet2_data = excel_data.parse('Accessioned_By_Clinician')

# Select the top 5 clinicians based on the number of patients (Grand_total)
top_clinicians = sheet2_data.nlargest(5, 'Grand_total')

# Extracting the top 5 clinicians
top_clinician_names = top_clinicians['Clinician'].unique()

# Filtering the data to include only the top 5 clinicians
top_clinicians_data = sheet2_data[sheet2_data['Clinician'].isin(top_clinician_names)]

# Grouping the data by clinician and aggregating the institution values
clinician_institutions = top_clinicians_data.groupby('Clinician')['Institution'].unique().reset_index()

# Grouping the data by institution and counting the number of unique clinicians in each institution
institution_clinician_count = sheet2_data.groupby('Institution')['Clinician'].nunique().sort_values(ascending=False)

# Selecting the top 20 institutions
top_20_institutions = institution_clinician_count.head(20)

# Creating an interactive bar chart
fig = px.bar(
    top_20_institutions,
    x=top_20_institutions.index,
    y=top_20_institutions.values,
    title='Top 20 Institutions with the Most Number of Clinicians',
    labels={'x': 'Institution', 'y': 'Number of Clinicians'},
    template='plotly_white'
)

# Updating the layout for better readability
fig.update_layout(xaxis_title='Institution', yaxis_title='Number of Clinicians', xaxis_tickangle=-45)
fig.update_traces(marker_color='skyblue')

# Showing the plot
fig.show()


# Load the Excel file
file_path = 'pr1.xlsx'
excel_data = pd.ExcelFile(file_path)
# Read the data from Sheet1
sheet1_data = excel_data.parse('Accessioned_By_Institute')

# Select the top 10 institutions based on the number of patients (Grand_total)
top_institutions = sheet1_data.nlargest(10, 'Grand_total')

# Selecting only the weekly patient data for the top 10 institutions
weekly_data = top_institutions.iloc[:, 5:-3].T
weekly_data.columns = top_institutions['Institution']

# Converting the index to datetime
weekly_data.index = pd.to_datetime(weekly_data.index)

# Resampling the data to get the sum for each quarter
quarterly_data = weekly_data.resample('Q').sum()

import plotly.graph_objects as go
from plotly.subplots import make_subplots
import plotly.express as px

# Create a subplot
fig = make_subplots(rows=1, cols=1)

# Define a colormap
colormap = px.colors.sequential.Viridis

# Add traces for each institution
for i, institution in enumerate(quarterly_data.columns):
    color = colormap[int(i * len(colormap) / len(quarterly_data.columns))]
    fig.add_trace(go.Scatter(x=quarterly_data.index, y=quarterly_data[institution], 
                             mode='lines+markers', name=institution, 
                             line=dict(color=color)))

# Update the layout
fig.update_layout(title_text='Time Series Patient Trend for Top 10 Institutions',
                  xaxis_title='Date',
                  yaxis_title='Number of Patients',
                  legend_title='Institution',
                  template='plotly')

# Show the plot
fig.show()


import plotly.express as px
import pandas as pd

data_sheet2 = pd.read_excel("pr1.xlsx", sheet_name='Accessioned_By_Clinician')
# Aggregating the total number of patients for each country
country_total_patients = data_sheet2.groupby('Country')['Grand_total'].sum().sort_values(ascending=False).reset_index()

# Visualization using Plotly
fig = px.bar(country_total_patients, x='Country', y='Grand_total', title='Distribution of Patients Across Countries')
fig.update_layout(xaxis_title='Country', yaxis_title='Total Number of Patients', autosize=False, width=1000, height=500)
fig.show()


df = pd.read_excel("pr1.xlsx", sheet_name='Patient_Results')

# Group by 'Accession_year' and count the number of patients in each year
patient_distribution = df['Accession_year'].value_counts().reset_index()
patient_distribution.columns = ['Year', 'Number of Patients']

# Sort the dataframe based on 'Year'
patient_distribution = patient_distribution.sort_values(by='Year')

# Create interactive bar plot
fig = px.bar(patient_distribution, x='Year', y='Number of Patients', title='Distribution of Patients Based on Year of Accession')
fig.show()


# Group by 'Institution_City' and count the number of patients in each city
patient_distribution = df['Institution_City'].value_counts().reset_index()
patient_distribution.columns = ['City', 'Number of Patients']

# Initialize the Dash app
app = Dash(__name__)

# Define the layout of the app
app.layout = html.Div([
    dcc.Dropdown(
        id='num-top-cities-dropdown',
        options=[{'label': f'Top {i}', 'value': i} for i in range(1, len(patient_distribution) + 1)] + 
                 [{'label': f'Bottom {i}', 'value': -i} for i in range(1, len(patient_distribution) + 1)],
        value=20,
        clearable=False,
        searchable=True,
        style={'width': '50%'}
    ),
    dcc.Graph(id='patient-distribution-bar-plot')
])

# Define the callback to update the graph based on the selected number of top cities
@app.callback(
    Output('patient-distribution-bar-plot', 'figure'),
    Input('num-top-cities-dropdown', 'value')
)
def update_graph(num_top_cities):
    # Filter the dataframe to get the top N or bottom N cities
    if num_top_cities > 0:
        filtered_cities = patient_distribution.head(num_top_cities)
    else:
        filtered_cities = patient_distribution.tail(-num_top_cities)
    
    # Create interactive bar plot
    fig = px.bar(filtered_cities, x='City', y='Number of Patients', title='Distribution of Patients Based on City')
    return fig

# Run the app
if __name__ == '__main__':
    app.run_server(debug=True)


import pandas as pd
import plotly.express as px
from dash import Dash, dcc, html
from dash.dependencies import Input, Output

# Group by 'Institution_ST' and count the number of patients in each state
patient_distribution = df['Institution_ST'].value_counts().reset_index()
patient_distribution.columns = ['State', 'Number of Patients']

# Initialize the Dash app
app = Dash(__name__)

# Define the layout of the app
app.layout = html.Div([
    dcc.Dropdown(
        id='num-top-states-dropdown',
        options=[{'label': f'Top {i}', 'value': i} for i in range(1, len(patient_distribution) + 1)] + 
                 [{'label': f'Bottom {i}', 'value': -i} for i in range(1, len(patient_distribution) + 1)],
        value=20,
        clearable=False,
        searchable=True,
        style={'width': '50%'}
    ),
    dcc.Graph(id='patient-distribution-bar-plot')
])

# Define the callback to update the graph based on the selected number of top states
@app.callback(
    Output('patient-distribution-bar-plot', 'figure'),
    Input('num-top-states-dropdown', 'value')
)
def update_graph(num_top_states):
    # Filter the dataframe to get the top N or bottom N states
    if num_top_states > 0:
        filtered_states = patient_distribution.head(num_top_states)
    else:
        filtered_states = patient_distribution.tail(-num_top_states)
    
    # Create interactive bar plot
    fig = px.bar(filtered_states, x='State', y='Number of Patients', title='Distribution of Patients Based on State')
    return fig

# Run the app
if __name__ == '__main__':
    app.run_server(debug=True)


import plotly.express as px
import pandas as pd

# Sample data for US states and their populations
data = {
    'State': ['California', 'Texas', 'Florida', 'New York', 'Pennsylvania', 'Illinois', 'Ohio', 'Georgia', 'North Carolina', 'Michigan',
              'New Jersey', 'Virginia', 'Washington', 'Arizona', 'Massachusetts', 'Tennessee', 'Indiana', 'Missouri', 'Maryland', 'Wisconsin'],
    'Population': [39538223, 29145505, 21538187, 20201249, 13002700, 12822739, 11799448, 10711908, 10439388, 10077331,
                    9294493, 8631393, 7693612, 7151502, 7029917, 6910840, 6785528, 6160281, 6185278, 5893718]
}

df = pd.DataFrame(data)

# Sort the DataFrame based on Population in descending order
df = df.sort_values(by='Population', ascending=False)

# Create the bar chart
fig = px.bar(df, x='State', y='Population', title='Population of Top 20 US States', text='Population')
fig.update_traces(texttemplate='%{text:,}', textposition='outside')
fig.update_layout(xaxis_title='State', yaxis_title='Population', yaxis=dict(type='linear'))
fig.show()


import pandas as pd
import plotly.express as px

# Read data
df_institute = pd.read_excel('pr1.xlsx', sheet_name='Accessioned_By_Institute')

# Calculate total patients for each institute
df_institute['Total_Patients'] = df_institute.iloc[:, 5:].sum(axis=1)

# Aggregate total patients by state
state_patient_counts = df_institute.groupby('State')['Total_Patients'].sum().reset_index()

# Create interactive choropleth map
fig = px.choropleth(state_patient_counts, 
                    locations='State', 
                    color='Total_Patients',
                    locationmode="USA-states", 
                    scope="usa",
                    title='Total Number of Patients Across All States',
                    color_continuous_scale="Viridis",
                    hover_name='State',
                    hover_data={'State': False, 'Total_Patients': True})
fig.update_geos(fitbounds="locations")
fig.show()


import pandas as pd
import plotly.express as px

# Load the data
df_patient_results = pd.read_excel("pr1.xlsx", sheet_name="Patient_Results")

# Create a bar plot for gender distribution
fig_gender = px.bar(df_patient_results['Sex'].value_counts().reset_index(), 
                    x='index', 
                    y='Sex', 
                    labels={'index': 'Gender', 'Sex': 'Count'},
                    title='Gender Distribution of Patients')
fig_gender.show()


# Prepare the data for geospatial visualization
state_gender_distribution = df_patient_results.groupby(['Institution_ST', 'Sex']).size().unstack().reset_index()
state_gender_distribution = state_gender_distribution.fillna(0)

# Create a choropleth map for males
fig_male = px.choropleth(state_gender_distribution, 
                         locations='Institution_ST', 
                         color='Male',
                         locationmode="USA-states", 
                         scope="usa",
                         title='Distribution of Male Patients Across States')
fig_male.show()

# Create a choropleth map for females
fig_female = px.choropleth(state_gender_distribution, 
                           locations='Institution_ST', 
                           color='Female',
                           locationmode="USA-states", 
                           scope="usa",
                           title='Distribution of Female Patients Across States')
fig_female.show()

Note: All visualizations are interactive so you may hover on them to get more information and even zoom, pan etc.¶

Institutional based trends¶

Patient Demographic based trends¶

Note: All visualizations are interactive so you may hover on them to get more information and even zoom, pan etc.¶

Gene related distributions¶

Clinician related distributions¶

Age groups related trends¶

Institutional based trends¶

Patient Demographic based trends¶

Gender related trends¶